package com.situ.company.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;

public final class JDBCUtil {

	private JDBCUtil() {
		
	}
	private static String url;
	private static String user;
	private static String password;
	private static String driver;
	
	static {
		ResourceBundle r=ResourceBundle.getBundle("com.situ.company.util.jdbc");
		
		url=r.getString("url");
		user=r.getString("user");
		password=r.getString("password");
		driver=r.getString("driver");
//		System.out.println(url);
//		System.out.println(user);
//		System.out.println(password);
//		System.out.println(driver);
		
		try {
			//1-加载驱动
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//2-建立链接
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url,user,password);
	}
	
	
	/**
	 * 执行DML
	 * @param sql
	 * @param vals
	 * @return
	 */
	public static Integer update(String sql,Object...  vals) {
		return update(sql,Arrays.asList(vals));
	}
	/**
	 * 执行DML
	 * 
	 * @param sql
	 * @return
	 */
	public static Integer update(String sql,List<Object> vals) {
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++) 
				ps.setObject(i+1, vals.get(i));
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			close(ps,conn);
		}
		return null;
	}
	
	/**
	 * 执行DQL
	 * @param <T>
	 * @param sql
	 * @param vals
	 * @param clazz 
	 * 			返回集合中的元素类型的类的描述
	 * @param fields
	 * 			key=Model属性名 value=数据库字段名
	 * @return
	 */
	public static <T> List<T> queryList(String sql,List<Object> vals,Class<T> clazz,Map<String,String> fields) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<T> result=new ArrayList<T>();
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++) 
				ps.setObject(i+1, vals.get(i));
			rs=ps.executeQuery();
			while(rs.next()) {
				//实例化
				T model=clazz.newInstance();
				//映射 key=model属性值 字段 value数据库字段
				for( Entry<String, String> entry:fields.entrySet()) {
					Field field = clazz.getDeclaredField(entry.getKey());
					field.setAccessible(true);
					field.set(model, rs.getObject(entry.getValue()));
				}
				result.add(model);
			}
		} catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			close(rs,ps,conn);
		}
		return result;
	}	
	/**
	 * 执行DQL查询一个
	 * @param <T>
	 * @param sql
	 * @param vals
	 * @param clazz
	 * @param fields
	 * @return
	 */
	public static <T> T queryModel(String sql,List<Object> vals,Class<T> clazz,Map<String,String> fields) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++) 
				ps.setObject(i+1, vals.get(i));
			rs=ps.executeQuery();
			if(rs.next()) {
				T model=clazz.newInstance();
				//映射
				for( Entry<String, String> entry:fields.entrySet()) {
					Field field = clazz.getDeclaredField(entry.getKey());
					field.setAccessible(true);
					field.set(model, rs.getObject(entry.getValue()));
				}
				return model;
			}
		} catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			close(rs,ps,conn);
		}
		return null;
	}	
	/**
	 * 集合中的属性一样，获取字段
	 * @param <T>
	 * @param sql
	 * @param vals
	 * @param clazz
	 * @param props
	 * @return
	 */
	public static <T> T queryModel(String sql,List<Object> vals,Class<T> clazz,List<String> props) {
		Map<String,String> fields =new HashMap<>();
		for(String p:props) {
			fields.put(p, p);
		}
		return queryModel(sql, vals, clazz, fields);
	}
	/**
	 * 反射获取映射关系
	 * @param <T>
	 * @param sql
	 * @param vals
	 * @param clazz
	 * @return
	 */
	public static <T> T queryModel(String sql,List<Object> vals,Class<T> clazz) {
		Map<String,String> fields =new HashMap<>();
		for(Field p:clazz.getDeclaredFields()) {
			fields.put(p.getName(), p.getName());
		}
		return queryModel(sql, vals, clazz, fields);
	}
	/**
	 * Model和数据库字段对应的一样
	 * @param <T>
	 * @param sql
	 * @param vals
	 * @param clazz 
	 * 			返回集合中的元素类型的类的描述
	 * @param fields
	 * 			key=Model属性名 value=数据库字段名
	 * @return
	 */
	public static <T> List<T> queryList(String sql,List<Object> vals,Class<T> clazz,List<String> props) {
		Map<String,String> fields =new HashMap<>();
		for(String p:props) {
			fields.put(p, p);
		}
		return queryList(sql, vals, clazz, fields);
	}
	
	public static Integer queryInt(String sql,List<Object> vals) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<vals.size();i++) 
				ps.setObject(i+1, vals.get(i));
			rs=ps.executeQuery();
			if(rs.next())
				//数据库字段从1开始索引
				return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public static void close(Statement st,Connection conn) {
		close(null,st,conn);
	}
	
	public static void close(ResultSet rs, Statement st,Connection conn) {
		
		if (rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (st!=null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}

